package com.han.dao;

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import org.apache.log4j.Logger;
import com.han.bom.MultipleChoice;
import com.han.bom.Question;
import com.han.bom.TrueOrFalse;

public class QuestionDAO {
	private Connection connection;
	private PreparedStatement preparedStatement;
	private ResultSet resultSet;
	private static Logger logger = Logger.getLogger(QuestionDAO.class);
	
	public void connect() throws ClassNotFoundException, SQLException{
		try{
			Class.forName("com.mysql.jdbc.Driver");
			this.connection = DriverManager.getConnection("jdbc:mysql://localhost:3306/beta_test_engine", "root", "");
		}catch(ClassNotFoundException e){
			logger.error(e.getMessage(), e);
			throw e;
		} catch (SQLException e) {
			logger.error(e.getMessage(), e);
			throw e;
		}
	}
	
	public void disconnect() throws SQLException{
		if(this.resultSet!=null){
			try{
				this.resultSet.close();
			}catch(SQLException e){
				logger.error(e.getMessage(), e);
				throw e;
			}
		}
		
		if(this.preparedStatement!=null){
			try {
				this.preparedStatement.close();
			} catch (SQLException e) {
				logger.error(e.getMessage(), e);
				throw e;
			}
		}
		
		if(this.connection!=null){
			try {
				this.connection.close();
			} catch (SQLException e) {
				logger.error(e.getMessage(), e);
				throw e;
			}
		}
	}
	
	public ArrayList<Question> retrieveAllQuestions() throws SQLException, ClassNotFoundException{
		ArrayList<Question> questions = new ArrayList<Question>();
		TrueOrFalse tf = null;
		MultipleChoice mc = null;
		Boolean type = null;
		
		try {
			this.connect();
			String select = "SELECT * FROM tbl_question";
			this.preparedStatement = this.connection.prepareStatement(select);
			this.resultSet = this.preparedStatement.executeQuery();
				
			while(this.resultSet.next()){
				type = this.resultSet.getBoolean(3);
				
				if(type){
					mc = new MultipleChoice();
					mc.setQuestionID("Q"+this.resultSet.getInt(1)+"MC");
					mc.setQuestion(this.resultSet.getString(2));
					mc.setCategory(this.resultSet.getString(4));
					questions.add(mc);
				}else
				{
					tf = new TrueOrFalse();
					tf.setQuestionID("Q"+this.resultSet.getInt(1)+"TF");
					tf.setQuestion(this.resultSet.getString(2));
					tf.setCategory(this.resultSet.getString(4));
					questions.add(tf);
				}
			}
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			logger.error(e.getMessage(), e);
			throw e;
		}
		finally{
			this.disconnect();
		}
		
		return questions;
	}
	
	public Question retrieveQuestionById(String questionid) throws SQLException, ClassNotFoundException{
		int qid = 0;
		Question question = null;
		Boolean type = null;
		
		try{
			qid = Integer.parseInt(questionid.replaceAll("[^\\d]", ""));
		}catch(NumberFormatException e){
			qid = 0;
		}
		
		try {
			this.connect();
			String select = "SELECT * FROM tbl_question WHERE question_id=?";
			this.preparedStatement = this.connection.prepareStatement(select);
			this.preparedStatement.setInt(1, qid);
			this.resultSet = this.preparedStatement.executeQuery();
				
			while(this.resultSet.next()){
				type = this.resultSet.getBoolean(3);
				
				if(type){
					question = new MultipleChoice();
					question.setQuestionID("Q"+this.resultSet.getInt(1)+"MC");
					question.setQuestion(this.resultSet.getString(2));
					question.setCategory(this.resultSet.getString(4));
				}else
				{
					question = new TrueOrFalse();
					question.setQuestionID("Q"+this.resultSet.getInt(1)+"TF");
					question.setQuestion(this.resultSet.getString(2));
					question.setCategory(this.resultSet.getString(4));
				}
			}
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			logger.error(e.getMessage(), e);
			throw e;
		}
		finally{
			this.disconnect();
		}
		
		return question;
	}
	
	public ArrayList<Question> retrieveQuestionsbyExam(String examid) throws SQLException, ClassNotFoundException{
		ArrayList<Question> questions = new ArrayList<Question>();
		int eid = 0;
		TrueOrFalse tf = null;
		MultipleChoice mc = null;
		Boolean type = null;
		
		try{
			eid = Integer.parseInt(examid.replaceAll("[^\\d]", ""));
		}catch(NumberFormatException e){
			eid = 0;
		}
		
		try {
			this.connect();
			String select = "SELECT * FROM tbl_question AS a INNER JOIN tbl_exam_questions AS b ON a.question_id=b.question_id WHERE b.exam_id=?";
			this.preparedStatement = this.connection.prepareStatement(select);
			
			this.preparedStatement.setInt(1, eid);
			this.resultSet = this.preparedStatement.executeQuery();
				
			while(this.resultSet.next()){
				type = this.resultSet.getBoolean(3);
					
				if(type){
					mc = new MultipleChoice();
					mc.setQuestionID("Q"+this.resultSet.getInt(1)+"MC");
					mc.setQuestion(this.resultSet.getString(2));
					mc.setCategory(this.resultSet.getString(4));
					questions.add(mc);
				}else
				{
					tf = new TrueOrFalse();
					tf.setQuestionID("Q"+this.resultSet.getInt(1)+"TF");
					tf.setQuestion(this.resultSet.getString(2));
					tf.setCategory(this.resultSet.getString(4));
					questions.add(tf);
				}
			}
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			logger.error(e.getMessage(), e);
			throw e;
		}
		finally{
			this.disconnect();
		}
		
		return questions;
	}
	
	public ArrayList<String> retrieveCategoriesbyExam(String examid) throws SQLException, ClassNotFoundException{
		ArrayList<String> categories = new ArrayList<String>();
		int eid = 0;
		
		try{
			eid = Integer.parseInt(examid.replaceAll("[^\\d]", ""));
		}catch(NumberFormatException e){
			eid = 0;
		}
		
		try {
			this.connect();
			String select = "SELECT DISTINCT a.question_category FROM tbl_question AS a INNER JOIN tbl_exam_questions AS b ON a.question_id=b.question_id WHERE b.exam_id=?";
			this.preparedStatement = this.connection.prepareStatement(select);
			
			this.preparedStatement.setInt(1, eid);
			this.resultSet = this.preparedStatement.executeQuery();
				
			while(this.resultSet.next()){
				categories.add(this.resultSet.getString(1));
			}
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			logger.error(e.getMessage(), e);
			throw e;
		}
		finally{
			this.disconnect();
		}
		
		return categories;
	}
	
	public ArrayList<Question> retrieveQuestionsbyProfessorId(String profid) throws SQLException, ClassNotFoundException{
		ArrayList<Question> questions = new ArrayList<Question>();
		int id = 0;
		TrueOrFalse tf = null;
		MultipleChoice mc = null;
		Boolean type = null;
		
		try{
			id = Integer.parseInt(profid.replaceAll("[^\\d]", ""));
		}catch(NumberFormatException e){
			id = 0;
		}
		
		try {
			this.connect();
			String select = "SELECT * FROM tbl_question AS a INNER JOIN tbl_exam_questions AS b ON a.question_id=b.question_id INNER JOIN tbl_exam AS c ON b.exam_id=c.exam_id WHERE c.professor_id=?";
			this.preparedStatement = this.connection.prepareStatement(select);
			
			this.preparedStatement.setInt(1, id);
			this.resultSet = this.preparedStatement.executeQuery();
				
			while(this.resultSet.next()){
				type = this.resultSet.getBoolean(3);
				
				if(type){
					mc = new MultipleChoice();
					mc.setQuestionID("Q"+this.resultSet.getInt(1)+"MC");
					mc.setQuestion(this.resultSet.getString(2));
					mc.setCategory(this.resultSet.getString(4));
					questions.add(mc);
				}else
				{
					tf = new TrueOrFalse();
					tf.setQuestionID("Q"+this.resultSet.getInt(1)+"TF");
					tf.setQuestion(this.resultSet.getString(2));
					tf.setCategory(this.resultSet.getString(4));
					questions.add(tf);
				}
			}
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			logger.error(e.getMessage(), e);
			throw e;
		}
		finally{
			this.disconnect();
		}
		
		return questions;
	}
	
	public ArrayList<Question> retrieveQuestionsbyKeyword(String key) throws SQLException, ClassNotFoundException{
		ArrayList<Question> questions = new ArrayList<Question>();
		int qid = 0;
		TrueOrFalse tf = null;
		MultipleChoice mc = null;
		Boolean type = null;
		String intkey = "%"+key+"%";
		
		try{
			qid = Integer.parseInt(key.replaceAll("[^\\d]", ""));
		}catch(NumberFormatException e){
			qid = 0;
		}
		
		try {
			this.connect();
			String select = "SELECT DISTINCT * FROM tbl_question AS a INNER JOIN tbl_choice AS b ON a.question_id = b.question_id WHERE a.question_id = ? OR a.question LIKE ? OR a.question_category LIKE ? OR b.answer LIKE ?";
			this.preparedStatement = this.connection.prepareStatement(select);
			
			this.preparedStatement.setInt(1, qid);
			this.preparedStatement.setString(2, intkey);
			this.preparedStatement.setString(3, intkey);
			this.preparedStatement.setString(4, intkey);
			this.resultSet = this.preparedStatement.executeQuery();
				
			while(this.resultSet.next()){
				type = this.resultSet.getBoolean(3);
				
				if(type){
					mc = new MultipleChoice();
					mc.setQuestionID("Q"+this.resultSet.getInt(1)+"MC");
					mc.setQuestion(this.resultSet.getString(2));
					mc.setCategory(this.resultSet.getString(4));
					questions.add(mc);
				}else
				{
					tf = new TrueOrFalse();
					tf.setQuestionID("Q"+this.resultSet.getInt(1)+"TF");
					tf.setQuestion(this.resultSet.getString(2));
					tf.setCategory(this.resultSet.getString(4));
					questions.add(tf);
				}
			}
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			logger.error(e.getMessage(), e);
			throw e;
		}
		finally{
			this.disconnect();
		}
		
		return questions;
	}
	
	public boolean insertQuestion(Question question) throws SQLException, ClassNotFoundException{
		boolean success = false;
		String insert = "INSERT INTO tbl_question(question,question_type,question_category) VALUES(?,?,?)";
		int row = 0;

		try {
			this.connect();
			this.connection.setAutoCommit(false);
			this.preparedStatement = this.connection.prepareStatement(insert);

			this.preparedStatement.setString(1, question.getQuestion());
				
			if(question instanceof MultipleChoice){
				this.preparedStatement.setBoolean(2, true);
			}else{
				this.preparedStatement.setBoolean(2, false);
			}
			this.preparedStatement.setString(3, question.getCategory());
			row += this.preparedStatement.executeUpdate();
			
			this.connection.commit();
			success = (row>0)?true:false;
		} catch (ClassNotFoundException e) {
			// TODO Auto-generated catch block
			this.connection.rollback();
			logger.error(e.getMessage(), e);
			throw e;
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			this.connection.rollback();
			logger.error(e.getMessage(), e);
			throw e;
		}finally{
			this.connection.setAutoCommit(true);
			this.disconnect();
		}
		return success;
	}
	
	public boolean updateQuestion(Question question) throws SQLException, ClassNotFoundException{
		boolean success = false;
		String insert = "UPDATE tbl_question SET question=?,question_category=? WHERE question_id=?";
		int id = 0;
		int row = 0;
		
		try{
			id = Integer.parseInt(question.getQuestionID().replaceAll("[^\\d]", ""));
		}catch(NumberFormatException e){
			id = 0;
		}

		try {
			this.connect();
			this.connection.setAutoCommit(false);
			this.preparedStatement = this.connection.prepareStatement(insert);
			
			this.preparedStatement.setString(1, question.getQuestion());
			this.preparedStatement.setString(2, question.getCategory());
			this.preparedStatement.setInt(3, id);
			row += this.preparedStatement.executeUpdate();
			
			this.connection.commit();
			success = (row>0)?true:false;
		} catch (ClassNotFoundException e) {
			// TODO Auto-generated catch block
			this.connection.rollback();
			logger.error(e.getMessage(), e);
			throw e;
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			this.connection.rollback();
			logger.error(e.getMessage(), e);
			throw e;
		}finally{
			this.connection.setAutoCommit(true);
			this.disconnect();
		}
		
		return success;
	}
	
	public boolean addQuestionToExam(String questionID, String examid) throws ClassNotFoundException, SQLException{
		boolean success = false;
		int id = 0;
		int eid = 0;
		String insert = "";
		int row = 0;
		
		try{
			id = Integer.parseInt(questionID.replaceAll("[^\\d]", ""));
		}catch(NumberFormatException e){
			id = 0;
		}
		
		try{
			eid = Integer.parseInt(examid.replaceAll("[^\\d]", ""));
		}catch(NumberFormatException e){
			eid = 0;
		}
		
		try {
			this.connect();
			this.connection.setAutoCommit(false);
			insert = "INSERT IGNORE INTO tbl_exam_questions(exam_id,question_id) VALUES((SELECT exam_id FROM tbl_exam WHERE exam_id=?),(SELECT question_id FROM tbl_question WHERE question_id=?))";
			this.preparedStatement = this.connection.prepareStatement(insert);
				
			this.preparedStatement.setInt(1, eid);
			this.preparedStatement.setInt(2, id);
			row += this.preparedStatement.executeUpdate();
			
			this.connection.commit();
			
			success = (row>0)?true:false;
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			this.connection.rollback();
			logger.error(e.getMessage(), e);
			throw e;
		}finally{
			this.connection.setAutoCommit(true);
			this.disconnect();
		}
		
		return success;
	}
	
	public boolean removeAllQuestionsFromExam(String examid) throws ClassNotFoundException, SQLException{
		boolean success = false;
		String insert = "";
		int eid = 0;
		int row = 0;
		
		try{
			eid = Integer.parseInt(examid.replaceAll("[^\\d]", ""));
		}catch(NumberFormatException e){
			eid = 0;
		}
		
		try {
			this.connect();
			this.connection.setAutoCommit(false);
			insert = "DELETE FROM tbl_exam_questions WHERE exam_id=(SELECT exam_id FROM tbl_exam WHERE exam_id=?)";
			this.preparedStatement = this.connection.prepareStatement(insert);
			
			this.preparedStatement.setInt(1, eid);
			row += this.preparedStatement.executeUpdate();
			this.connection.commit();
			success = (row>0)?true:false;
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			this.connection.rollback();
			logger.error(e.getMessage(), e);
			throw e;
		}finally{
			this.disconnect();
		}
		
		return success;
	}
	
	public boolean removeQuestionFromExam(String questionID, String examid) throws ClassNotFoundException, SQLException{
		boolean success = false;
		String insert = "";
		int id = 0;
		int eid = 0;
		int row = 0;
		
		try{
			id = Integer.parseInt(questionID.replaceAll("[^\\d]", ""));
		}catch(NumberFormatException e){
			id = 0;
		}
		
		try{
			eid = Integer.parseInt(examid.replaceAll("[^\\d]", ""));
		}catch(NumberFormatException e){
			eid = 0;
		}
		
		try {
			this.connect();
			this.connection.setAutoCommit(false);
			insert = "DELETE FROM tbl_exam_questions WHERE question_id=(SELECT question_id FROM tbl_question WHERE question_id=?) AND exam_id=(SELECT exam_id FROM tbl_exam WHERE exam_id=?)";
			this.preparedStatement = this.connection.prepareStatement(insert);
			
			this.preparedStatement.setInt(1, id);
			this.preparedStatement.setInt(2, eid);
			row += this.preparedStatement.executeUpdate();
			this.connection.commit();
			success = (row>0)?true:false;
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			this.connection.rollback();
			logger.error(e.getMessage(), e);
			throw e;
		}finally{
			this.disconnect();
		}
		
		return success;
	}
	
	public int retrieveMaxQuestionID() throws ClassNotFoundException, SQLException{
		int id = 0;
		
		try {
			this.connect();
			String select = "SELECT AUTO_INCREMENT FROM information_schema.TABLES WHERE TABLE_SCHEMA = 'beta_test_engine' AND TABLE_NAME = 'tbl_question'";
			this.preparedStatement = this.connection.prepareStatement(select);
			this.resultSet = this.preparedStatement.executeQuery();
			
			while(resultSet.next()){
				id = this.resultSet.getInt(1);
			}
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			logger.error(e.getMessage(), e);
			throw e;
		}
		finally{
			this.disconnect();
		}
		
		return id;
	}

	public Connection getConnection() {
		return connection;
	}

	public void setConnection(Connection connection) {
		this.connection = connection;
	}

	public PreparedStatement getPreparedStatement() {
		return preparedStatement;
	}

	public void setPreparedStatement(PreparedStatement preparedStatement) {
		this.preparedStatement = preparedStatement;
	}

	public ResultSet getResultSet() {
		return resultSet;
	}

	public void setResultSet(ResultSet resultSet) {
		this.resultSet = resultSet;
	}
}
